﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data;


namespace CommunitySystem.BO.EmpbasicBO
{
    public class ExportPersonnelInfoBO
    {
        public int _id;
        public ExportPersonnelInfoBO(int id)
        {
            id = _id;
        }
        private byte[] _content = null;
        public FileTransferStream GetExcelFile(int id, string username)
        {
            FileTransferStream ftfs = new FileTransferStream();
            if (_content != null && id == _id)
            {
            }
            else
            {
                HSSFWorkbook workbook = null;             

                MemoryStream ms = new MemoryStream(ResourceFile.testexcel);
                workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                ms.Dispose();
                GetInfo(id, workbook);
              
                string strFileName = AppDomain.CurrentDomain.BaseDirectory + username + ".xls";
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                    
                }
                using (FileStream file = File.Open(strFileName, FileMode.Open))
                {
                    BinaryReader r = new BinaryReader(file);

                    r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

                    _content = r.ReadBytes((int)r.BaseStream.Length);
                }
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }
               
               
                ftfs.IsFinished = true;
                ftfs.FileContent = _content;
                
            }
            return ftfs;
        }


        private DataSet GetInfo(int id, NPOI.HSSF.UserModel.HSSFWorkbook workbook)
        {
            HSSFSheet sheet1 = workbook.GetSheetAt(0);
           
            string basicSql = string.Format(@"select emp.Name EmpName,emp.Gender,convert(varchar(10), emp.Birthday, 23) as Birthday,datediff(d,Birthday,GETDATE())/365 - 1 Age,emp.NativePlace,
emp.Native,emp.Political,emp.Health,CONVERT(varchar(12) , emp.WorkDate, 23) WorkDate,CONVERT(varchar(12),emp.NowWorkDate,23) NowWorkDate 
,emp.CardNo,o.Name OrgName,emp.PositionName,duty.DutyName, CONVERT(varchar(12) , duty.OKDate, 23) OKDate,duty.DutyLevel, CONVERT(varchar(12) ,duty.JobDate, 23) JobDate,emp.Phone,
emp.RegisterNo,emp.RegisterDate, emp.HomeAddress, emp.HasPhoto, emp.PhotoName
 from Emp_BasicInfo emp
 inner join OrgForHR o on o.ID = emp.OrgID
 left join Emp_Duty duty on duty.EmpbasicID = emp.ID and duty.jobstatus='在任'
 where emp.ID= {0}", id);
           
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(basicSql);
            #region 基本信息
            HSSFRow row4 = sheet1.GetRow(3);
            HSSFCell cell4d = row4.GetCell(3);
            SetCellValue(cell4d, ds.Tables[0].Rows[0]["EmpName"]); //cell4d.SetCellValue(ds.Tables[0].Rows[0][0].ToString());
            HSSFCell cell4h = row4.GetCell(7);
            SetCellValue(cell4h, ds.Tables[0].Rows[0]["Gender"]);
            SetCellValue(sheet1, 4, 'L', ds.Tables[0].Rows[0], "Birthday");
            SetCellValue(sheet1, 5, 'L', ds.Tables[0].Rows[0], "Age");
            SetCellValue(sheet1, 6, 'D', ds.Tables[0].Rows[0], "NativePlace");
            SetCellValue(sheet1, 6, 'H', ds.Tables[0].Rows[0], "Native");
            SetCellValue(sheet1, 6, 'L', ds.Tables[0].Rows[0], "Political");
            SetCellValue(sheet1, 7, 'D', ds.Tables[0].Rows[0], "Health");
            SetCellValue(sheet1, 7, 'H', ds.Tables[0].Rows[0], "WorkDate");
            SetCellValue(sheet1, 7, 'L', ds.Tables[0].Rows[0], "NowWorkDate");
            SetCellValue(sheet1, 8, 'D', ds.Tables[0].Rows[0], "CardNo");
            //SetCellValue(sheet1, 8, 'L', ds.Tables[0].Rows[0], "CardNo");
            string phone = ds.Tables[0].Rows[0]["Phone"] as string;
            if (!string.IsNullOrEmpty(phone))
            {
                string[] phoneCall = phone.Split('\\');
                if (phoneCall.Length == 2)
                {
                    SetCellValue(sheet1, 8, 'L', phoneCall[1]);
                    SetCellValue(sheet1, 10, 'N', phoneCall[0]);
                }
                else
                {
                    if (phoneCall[0].Length == 11)
                        SetCellValue(sheet1, 8, 'L', phoneCall[0]);
                    else
                        SetCellValue(sheet1, 10, 'N', phoneCall[0]);
                }
            }
            SetCellValue(sheet1, 10, 'D', ds.Tables[0].Rows[0], "HomeAddress");
            string Org = ds.Tables[0].Rows[0]["OrgName"] as string;
            string Position = ds.Tables[0].Rows[0]["DutyName"] as string;
            if (!string.IsNullOrEmpty(Org) && !string.IsNullOrEmpty(Position))
            {
                SetCellValue(sheet1, 11, 'E', Org + ":" + Position);
            }
            else
            {
                SetCellValue(sheet1, 11, 'E', Org + Position);
            }
            SetCellValue(sheet1, 11, 'N', ds.Tables[0].Rows[0], "OKDate");

            SetCellValue(sheet1, 12, 'E', ds.Tables[0].Rows[0], "DutyLevel");
            SetCellValue(sheet1, 12, 'N', ds.Tables[0].Rows[0], "JobDate");
            #endregion

            #region 图片
            try
            {
                bool? hasPhoto = ds.Tables[0].Rows[0]["HasPhoto"] as bool?;
                if (hasPhoto == true)
                {
                    string photoname = ds.Tables[0].Rows[0]["PhotoName"] as string;
                    string extend = photoname.Split('.')[1];
                    string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings["photo"].ToString();
                    string fileName = uploadFolder + @"\" + photoname;
                    byte[] bytes = System.IO.File.ReadAllBytes(fileName);
                    HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
                    int picFormat = 0;
                    //workbook.AddPicture(bytes, 
                    if (!string.IsNullOrEmpty(extend))
                    {
                        switch (extend.ToLower())
                        {
                            case "png":
                                picFormat = HSSFWorkbook.PICTURE_TYPE_PNG;
                                break;
                            case "bmp":
                                picFormat = HSSFWorkbook.PICTURE_TYPE_PICT;
                                break;
                            case "jpg":
                                picFormat = HSSFWorkbook.PICTURE_TYPE_JPEG;
                                break;
                        }
                    }
                    int pictureIdx = workbook.AddPicture(bytes, picFormat);
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 13, 3, 14, 8);
                    HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
                }
            }
            catch (Exception ex)
            {

            }
            #endregion

            #region 教育信息

            string EdSql = string.Format(@"select * from Emp_Education ed where IsTopEducation=1 and EmpBasicID={0};
 select Isnull(DegreeName, '') as DegreeName, Isnull(LearningType, '') as LearningType, 
 Isnull(School, '') as School, Isnull(Professional, '') as Professional from Emp_Degree where EmpbasicID={0}", id);
            DataSet Edds = AccessHelper.GetInstance().ExcuteDataSet(EdSql);
            #region 学位信息
            List<DataRow> listFull = new List<DataRow>();
            List<DataRow> job = new List<DataRow>();
            if (Edds.Tables[1].Rows.Count > 0)
            {
                foreach (DataRow dr in Edds.Tables[1].Rows)
                {
                    string type = dr["LearningType"] as string;
                    if (type.Equals("正规高等院校（全日制）"))
                    {
                        listFull.Add(dr);
                    }
                    else
                    {
                        job.Add(dr);
                    }
                }
                if (listFull.Count > 0)
                {
                    DataRow dr = GetTopDegree(listFull);
                    if (dr != null)
                    {
                        string school = dr["School"] as string;
                        string majoy = dr["Professional"] as string;
                        string s_m = string.Empty;
                        if (!string.IsNullOrEmpty(school) && !string.IsNullOrEmpty(majoy))
                        {
                            s_m = school + ":" + majoy;
                        }
                        else
                        {
                            s_m = school + majoy;
                        }
                        SetCellValue(sheet1, 14, 'E', dr, "DegreeName");
                        SetCellValue(sheet1, 13, 'K', s_m);
                    }
                }
                if (job.Count > 0)
                {
                    DataRow dr = GetTopDegree(job);
                    if (dr != null)
                    {
                        string school = dr["School"] as string;
                        string majoy = dr["Professional"] as string;
                        string s_m = string.Empty;
                        if (!string.IsNullOrEmpty(school) && !string.IsNullOrEmpty(majoy))
                        {
                            s_m = school + ":" + majoy;
                        }
                        else
                        {
                            s_m = school + majoy;
                        }
                        SetCellValue(sheet1, 16, 'E', dr, "DegreeName");
                        SetCellValue(sheet1, 15, 'K', s_m);
                    }
                }

            }
                


            #endregion 

            #region 学历信息
            if (Edds.Tables[0].Rows.Count > 0)
            {
                string educationtype = (Edds.Tables[0].Rows[0]["EducationType"] as string).Trim();
                string school = Edds.Tables[0].Rows[0]["School"] as string;
                string majoy = Edds.Tables[0].Rows[0]["Professional"] as string;
                string s_m = string.Empty;
                if (!string.IsNullOrEmpty(school) && !string.IsNullOrEmpty(majoy))
                {
                    s_m = school + ":" + majoy;
                }
                else
                {
                    s_m = school  + majoy;
                }
                if (educationtype.Equals("正规高等院校（全日制）"))
                {
                    SetCellValue(sheet1, 13, 'E', Edds.Tables[0].Rows[0], "EducationName");
                    SetCellValue(sheet1, 13, 'K',  s_m);
                }
                else
                {
                    SetCellValue(sheet1, 15, 'E', Edds.Tables[0].Rows[0], "EducationName");
                    SetCellValue(sheet1, 15, 'K', s_m);
                }
            }
            #endregion

           

            #endregion

            #region 工作经历
            string WorkExpienceSql = string.Format(@"select CONVERT(varchar(10), resume.BeginDate, 102) as BeginDate, 
CONVERT(varchar(10), resume.EndDate, 102) as EndDate,
resume.Unit, resume.Duty, resume.Duty, resume.IsPeriod from Emp_BasicInfo emp 
left join Emp_Resume resume on emp.ID = resume.EmpbasicID 
where emp.ID = {0} order by resume.BeginDate ", id);
            DataSet dsWE = AccessHelper.GetInstance().ExcuteDataSet(WorkExpienceSql);
            if (dsWE.Tables[0].Rows.Count > 0)
            {
                string _TimeTotal = string.Empty;
                string ContentTotal = string.Empty;
                foreach (DataRow dr in dsWE.Tables[0].Rows)
                {
                    string begindate = dr["BeginDate"] as string;
                    string enddate = dr["EndDate"] as string;
                    string time = string.Empty;
                    if (string.IsNullOrEmpty(begindate) && string.IsNullOrEmpty(enddate))
                    {
                        time = "";
                    }
                    else if (!string.IsNullOrEmpty(begindate) && !string.IsNullOrEmpty(enddate))
                    {
                        time = begindate + " - " + enddate;
                    }
                    else
                    {
                        time = begindate  + enddate;
                        if (dsWE.Tables[0].Rows.IndexOf(dr) == dsWE.Tables[0].Rows.Count - 1 && !string.IsNullOrEmpty(begindate) && string.IsNullOrEmpty(enddate))//最后一次
                        {
                            time = begindate + " 至今";
                        }
                    }
                    bool IsQiJian = (bool)dr["IsPeriod"];
                    string unit = dr["Unit"] as string;
                    string Duty = dr["Duty"] as string;
                    string content = string.Empty;
                    if (!string.IsNullOrEmpty(unit) && !string.IsNullOrEmpty(Duty))
                    {
                        content = unit + " " + Duty;

                    }
                    else if (!string.IsNullOrEmpty(unit) || !string.IsNullOrEmpty(Duty))
                    {
                        content = unit  + Duty;
                    }

                    if (IsQiJian)
                    {
                        ContentTotal += "(" + time + "  " + content + ")\n\t";
                    }
                    else
                    {
                        //_TimeTotal += time + "\n\t";
                        ContentTotal +=time +"  "+ content + "\n\t";
                    }
                }
                SetCellValue(sheet1, 17, 'C', ContentTotal);
                //SetCellValue(sheet1, 17, 'I', ContentTotal);
            }

            #endregion

            #region 考核情况
            HSSFSheet sheet2 = workbook.GetSheetAt(1);
            string sqlKaoHe = string.Format(@"select top 3 ass.AssessmentType, ass.AssessmentResult, Convert(varchar(10),ass.AssessmentDate,23) AssessmentDate from Emp_BasicInfo emp 
left join Emp_Assessment ass on ass.EmpbasicID = emp.ID
where CONVERT(int, SUBSTRING(CONVERT(varchar(12),ass.AssessmentDate, 23),0,5)) 
< CONVERT(int, SUBSTRING(CONVERT(varchar(12),GETDATE(), 23),0,5))
and  (CONVERT(int, SUBSTRING(CONVERT(varchar(12),GETDATE(), 23),0,5))-3) <
CONVERT(int, SUBSTRING(CONVERT(varchar(12),ass.AssessmentDate, 23),0,5)) and ass.EmpbasicID = {0}
order by ass.AssessmentDate desc", id);
            DataSet dsKaoHe = AccessHelper.GetInstance().ExcuteDataSet(sqlKaoHe);
            if (dsKaoHe.Tables[0].Rows.Count > 0)
            {
                int rowIndex = 2;
                foreach (DataRow dr in dsKaoHe.Tables[0].Rows)
                {
                    //string Datetime = dr["AssessmentDate"] as string;
                    //string result = dr["AssessmentResult"] as string;
                    SetCellValue(sheet2, rowIndex, 'C', dr, "AssessmentDate");
                    SetCellValue(sheet2, rowIndex, 'D', dr, "AssessmentResult");
                    rowIndex++;
                }
            }
            
            #endregion

            #region 奖惩
            string sqlJiangChen = string.Format(@"select ass.RewardNo, ass.RewardReason,   CONVERT(varchar(10), ass.OKDate,23) as OKDate,
OKUnit, CONVERT(varchar(10), ass.OKDate,23)+' '+ OKUnit+ ' '+ass.RewardReason+' '+ass.RewardNo as result
from Emp_BasicInfo emp 
left join Emp_RewardInfo ass on ass.EmpbasicID = emp.ID
where CONVERT(int, SUBSTRING(CONVERT(varchar(12),ass.OKDate, 23),0,5)) 
< CONVERT(int, SUBSTRING(CONVERT(varchar(12),GETDATE(), 23),0,5))
and  (CONVERT(int, SUBSTRING(CONVERT(varchar(12),GETDATE(), 23),0,5))-3) <
CONVERT(int, SUBSTRING(CONVERT(varchar(12),ass.OKDate, 23),0,5))  and ass.EmpbasicID={0}", id);
            DataSet dsJC = AccessHelper.GetInstance().ExcuteDataSet(sqlJiangChen);
            if (dsJC.Tables[0].Rows.Count > 0)
            {
                string contentTotal = string.Empty;
                foreach (DataRow dr in dsJC.Tables[0].Rows)
                {
                    string date = dr["OKDate"] as string;

                    string content = dr["result"] as string;
                    contentTotal += date + "  " + date + "\n\t";
                }
                SetCellValue(sheet2, 5, 'C', contentTotal);
            }

            #endregion

            #region 备注
            string des = "公务员登记号:" + "1234567890123" + "\n\t" + "公务员登记时间：" + "1975-01-01";
            
            #endregion
            return null;
        }

        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFCell cell, object value)
        {
            if (value != null)
            {
                cell.SetCellValue(value.ToString());
            }
            return true;
        }

        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFSheet sheet, int row, char column, DataRow dr, string drColumn)
        {
            NPOI.HSSF.UserModel.HSSFRow hrow = sheet.GetRow(row - 1);
            if (hrow != null)
            {
                HSSFCell cell = hrow.GetCell((int)(column - 'A'));
                if (cell != null)
                {
                    SetCellValue(cell, dr[drColumn]);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }

        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFSheet sheet, int row, char column, object value)
        {
            NPOI.HSSF.UserModel.HSSFRow hrow = sheet.GetRow(row - 1);
            if (hrow != null)
            {
                NPOI.HSSF.UserModel.HSSFCell cell = hrow.GetCell((int)(column - 'A'));
                if (cell != null)
                {
                    SetCellValue(cell, value);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }

        private DataRow GetTopDegree(List<DataRow> list)
        {
            DataRow drMaster = null;
            DataRow drBecholor = null;
            foreach (DataRow dr in list)
            {
                string degree = dr["DegreeName"] as string;
                if (degree.Contains("博士"))
                {
                    return dr;
                }
                if (degree.Contains("硕士"))
                {
                    drMaster =  dr;
                }
                if (degree.Contains("学士"))
                {
                    drBecholor = dr;
                }
               

            }
            if (drMaster != null)
            {
                return drMaster;
            }
            if (drBecholor != null)
                return drBecholor;
            return null;
        }

        /// <summary>
        /// 导出人员简历信息，附带职位信息
        /// </summary>
        /// <param name="empid"></param>
        /// <param name="position"></param>
        /// <returns></returns>
        public string ExportPersonnelInfoPosition(int empid, int position)
        {
            using (MemoryStream ms = new MemoryStream(ResourceFile.cvwithposition))
            {
                HSSFWorkbook workbook = null;
                workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                var sheet = workbook.GetSheetAt(0);
                #region 职位信息
                PublicOfficialSelectionBO bo = new PublicOfficialSelectionBO();
                var p  = bo.GetPublicOfficialSelection("0", position, 0, 0, -1, 0, "false", 0).FirstOrDefault();
                if (p != null)
                {
                    SetCellValue(sheet, 1, 'E', p.OrgName);
                    SetCellValue(sheet, 1, 'K', p.Department);
                    SetCellValue(sheet, 1, 'P', p.Duty);
                }
                #endregion
                string name = InsertInfo(empid, workbook);
                 string strFileName = AppDomain.CurrentDomain.BaseDirectory + name  + "简历.xls";
                 if (File.Exists(strFileName))
                     File.Delete(strFileName);
                 using (Stream stream = File.OpenWrite(strFileName))
                 {
                     workbook.Write(stream);
                 }
                 return name + "简历.xls";

            }
                

            return "";
        }


        private string InsertInfo(int id, NPOI.HSSF.UserModel.HSSFWorkbook workbook)
        {
            string sql = "select * from Emp_BasicInfoRegister where id = " + id;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            var sheet1 = workbook.GetSheetAt(0);
            #region 数据
            if (ds.Tables[0].Rows.Count > 0)
            {
                int rowindex = 5;//第5行
                var info = AccessHelper.FillDataRowToEntity<Emp_BasicInfoRegister>(ds.Tables[0], ds.Tables[0].Rows[0]);
                SetCellValue(sheet1, rowindex, 'D', info.Name);
                SetCellValue(sheet1, rowindex, 'H', info.Gender);                
                if(info.Birthday != null)
                    SetCellValue(sheet1, rowindex, 'L', ((DateTime)info.Birthday).ToString("yyyy-MM-dd"));   
            
                #region 图片
                try
                {
                    if (info.HasPhoto == true)
                    {
                        string photoname = info.PhotoName;
                        string extend = photoname.Split('.')[1];
                        string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings["photo"].ToString();
                        string fileName = uploadFolder + @"\" + photoname;
                        byte[] bytes = System.IO.File.ReadAllBytes(fileName);
                        HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
                        int picFormat = 0;
                        //workbook.AddPicture(bytes, 
                        if (!string.IsNullOrEmpty(extend))
                        {
                            switch (extend.ToLower())
                            {
                                case "png":
                                    picFormat = HSSFWorkbook.PICTURE_TYPE_PNG;
                                    break;
                                case "bmp":
                                    picFormat = HSSFWorkbook.PICTURE_TYPE_PICT;
                                    break;
                                case "jpg":
                                    picFormat = HSSFWorkbook.PICTURE_TYPE_JPEG;
                                    break;
                            }
                        }
                        int pictureIdx = workbook.AddPicture(bytes, picFormat);
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 13, rowindex, 14, rowindex + 5);
                        HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
                    }
                }
                catch (Exception ex)
                {

                }
                #endregion

                rowindex++;//第六行
                if (info.Birthday != null)
                {
                    var monthdiff = DateTime.Now.Month - ((DateTime)info.Birthday).Month;
                    int age = 0;
                    if (monthdiff >= 0)
                    {
                        age = DateTime.Now.Year - ((DateTime)info.Birthday).Year;
                    }
                    else
                    {
                        age = DateTime.Now.Year - ((DateTime)info.Birthday).Year -1;
                    }
                    SetCellValue(sheet1, rowindex, 'L', age);
                }
                rowindex++;//第七行
                SetCellValue(sheet1, rowindex, 'D', info.NativePlace);
                SetCellValue(sheet1, rowindex, 'H', info.Native);
                SetCellValue(sheet1, rowindex, 'L', info.Political);
                rowindex++;//第八行
                SetCellValue(sheet1, rowindex, 'D', info.Health);
                if(info.WorkDate != null)
                    SetCellValue(sheet1, rowindex, 'H', ((DateTime)info.WorkDate).ToShortDateString());
                if(info.NowWorkDate != null)
                    SetCellValue(sheet1, rowindex, 'L', ((DateTime)info.NowWorkDate).ToShortDateString());
                rowindex++;//第九行
                SetCellValue(sheet1, rowindex, 'D', info.CardNo);                
                SetCellValue(sheet1, rowindex, 'L', info.Phone);
                rowindex += 2;//第11行
                SetCellValue(sheet1, rowindex, 'D', info.HomeAddress);
                SetCellValue(sheet1, rowindex, 'N', info.LXDH);
                rowindex++;//第12行
                SetCellValue(sheet1, rowindex, 'E', info.CurrentJobAndPosition);
                if(info.OKDate != null)
                    SetCellValue(sheet1, rowindex, 'N', ((DateTime)info.OKDate).ToShortDateString());
                rowindex++;//第13行
                SetCellValue(sheet1, rowindex, 'E', info.DutyLevel);
                if(info.JobDate != null)
                    SetCellValue(sheet1, rowindex, 'N', ((DateTime)info.JobDate).ToShortDateString());
                rowindex++;//第14行
                SetCellValue(sheet1, rowindex, 'E', info.FullEducationBg +"\n"+info.FullDegree);
                SetCellValue(sheet1, rowindex, 'K', info.FullSchoolAndMajor);
                rowindex += 2;//第16行
                SetCellValue(sheet1, rowindex, 'E', info.PartEducationBg + "\n" + info.PartDegree);
                SetCellValue(sheet1, rowindex, 'K', info.PartSchoolAndMajor);
                rowindex += 2;//第18行
                SetCellValue(sheet1, rowindex, 'C', info.WorkExperice);
            

                ///sheet2
                var sheet2 = workbook.GetSheetAt(1);
                rowindex = 2;
                SetCellValue(sheet2, rowindex, 'C', ((DateTime)info.KaoHeTime1).Year+"年");
                SetCellValue(sheet2, rowindex, 'D', info.KaoHeResult1);
                rowindex += 1;
                SetCellValue(sheet2, rowindex, 'C', ((DateTime)info.KaoHeTime2).Year + "年");
                SetCellValue(sheet2, rowindex, 'D', info.KaoHeResult2);
                rowindex += 1;
                SetCellValue(sheet2, rowindex, 'C', ((DateTime)info.KaoHeTime3).Year + "年");
                SetCellValue(sheet2, rowindex, 'D', info.KaoHeResult3);
                rowindex += 1;
                SetCellValue(sheet2, rowindex, 'C', info.Jiangcheng);
                rowindex += 3;
                SetCellValue(sheet2, rowindex, 'C', info.Training);
                rowindex += 6;
                SetCellValue(sheet2, rowindex, 'C', info.Fuli);
                rowindex += 6;
                SetCellValue(sheet2, rowindex, 'C', info.Dsc);
                return info.Name;
            }
            #endregion

            return null;
        }
    }
}
